﻿using System.Data;
using System.Data.SqlClient;


namespace AccessControl.DataAcess
{
   public  class GroupAccess
    {

       public static DataTable SelectFull()
       {
           using (var cn = new SqlConnection(Util.ConnectionString))
           {
               var cmdSelect = new SqlCommand("SELECT ((CAST (fkGroupType as varchar))+'-'+(CAST (pkGroup as varchar)) ) as FullPK ,tblGroupType.*,tblGroup.* FROM tblGroup INNER JOIN tblGroupType ON fkGroupType=PkGRoupType ORDER BY GroupTypeName,GroupName", cn);
               var tbl = new DataTable();
               try
               {
                   cn.Open();

                   var adp = new SqlDataAdapter(cmdSelect);
                   adp.Fill(tbl);
               }

               finally
               {
                   cn.Close();
               }
               return tbl;
           }
       }

       public static DataTable SelectFullVisible()
       {
           using (var cn = new SqlConnection(Util.ConnectionString))
           {
               var cmdSelect = new SqlCommand("SELECT ((CAST (fkGroupType as varchar))+'-'+(CAST (pkGroup as varchar)) ) as FullPK ,tblGroupType.*,tblGroup.* FROM tblGroup INNER JOIN tblGroupType ON fkGroupType=PkGRoupType WHERE VisibleInFilter=1 ORDER BY GroupTypeName,GroupName", cn);
               var tbl = new DataTable();
               try
               {
                   cn.Open();

                   var adp = new SqlDataAdapter(cmdSelect);
                   adp.Fill(tbl);
               }

               finally
               {
                   cn.Close();
               }
               return tbl;
           }
       }
    }
}
